
Seafowl has supported UDFs [for some time now](https://www.splitgraph.com/blog/seafowl-wasm-udfs), and they continue to offer an exciting way for Seafowl users to significantly extend functionality in a fully sandboxed environment. Recently, in response to user request, we added a Golang example.

<ButtonLink href="https://github.com/splitgraph/seafowl-udf-go/">
  See the code
</ButtonLink>

<br />

[See the UDF docs](https://seafowl.io/docs/guides/custom-udf-wasm).

To learn more about compiling Golang to WASM, read on.

## Handling UDF requirements
There are certain requirements incumbent on the UDF implementer when it comes to running UDFs in Seafowl, and Golang is included. Today we cover a partial list:
- reading data in from the host through linear raw memory
- returning the computed result as a msgpack-encoded buffer
- working around Golang default syntax (e.g. TitleCase function exports)
- compiling Golang into WASM

## Reading data in
As discussed previously, when you register a Seafowl UDF it's required to [specify certain parameters](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/create_udf.sh#L11-L16) including e.g. `wasm_export` (e.g. addi64), `return_type` (e.g. BIGINT), `input_types` (e.g. BIGINT).

This is important because raw memory is the medium for passing data between Seafowl and the UDF. By specifying the parameter types upfront at UDF registration time, both Seafowl host and UDF know how to properly encode/decode the data they share with each other. That's why the function [`addi64`](https://github.com/splitgraph/seafowl-udf-go/blob/master/main.go#L12), expects a _pointer to a buffer_ vs conventional Go function params.

## Writing data out
Conversely, when we return the result to Seafowl, we need to return a buffer that includes the length of the result [first](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/udf.go#L115), with the actual msgpack-encoded result appended [afterwards](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/udf.go#L14).

This allows Seafowl's query engine to return the result of the UDF in the typical way.

## TitleCase function export workaround
Golang conventionally uses TitleCase to indicate when a function should be exported from its module scope. Some may find it [cute](https://youtu.be/M3BM9TB-8yA?t=879) and this convention is presumably fine in traditional Golang, but in certain contexts like UDFs, we are required to provide `alloc`/`dealloc` functions _in lower case_ - which poses a fun hurdle.

Fortunately, while researching how `tinygo` works, I learned it supports annotating functions regardless of casing via
`//export $functionName`

For example:
```go
//export alloc
func alloc(size uintptr) unsafe.Pointer {
	...
}
```

And Seafowl will be able to call this function in the required way.

## Compiling Golang into WASM
In August 2023 the Golang team shipped WASI/WASM [support](https://go.dev/blog/go1.21#a-new-port-to-wasi), so the following approach we used may no longer be strictly necessary these days.

When we tackled this project, Golang did not yet support WASM natively so looking elsewhere, we found [Tinygo](https://tinygo.org/docs/concepts/faq/what-is-tinygo/).

It turns out we can generate a WASM module ready for import into Seafowl via:
`tinygo build -o seafowl-udf-go.wasm -target=wasi`

NOTE: it's necessary to include `-target=wasi`.

## Recap

In this post we reviewed some of the steps taken to port the [Rust-based UDF](https://github.com/splitgraph/seafowl-udf-rust) into Golang. This was my first Golang project and I appreciated the opportunity to work at the lower level, learn about byteslice manipulation, msgpack serialization, WASI and more.

If you build a cool UDF, please consider letting us know!


#### Image credits
<ValeIgnore>[liantomtit](https://unsplash.com/@liantomtit), [evanescentlight](https://unsplash.com/@evanescentlight)</ValeIgnore>


We return to the wide world of WASM. In our previous round we implemented a UDF using Rust; today we're porting it to Golang.

Writing UDFs in Golang


## Introduction

[Seafowl](https://seafowl.io) is a new analytics database based on [DataFusion](https://arrow.apache.org/datafusion/) and [delta-rs](https://github.com/delta-io/delta-rs), designed for running "at the edge" and serving queries over HTTP with cache-optimized responses. Because it separates storage from compute, it's an ideal candidate for running in "serverless" environments. In this post, we'll explore the architecture behind Seafowl and demonstrate how to deploy it to Google Cloud Run (GCR) as a true "scale-to-zero" serverless database that doesn't require any persistent compute resources, and can query data stored in Google Cloud Storage (GCS), while minimizing cold-start times.

## Why do we need a serverless database?

The advantage of serverless applications is that each instance of an application can execute in a region close to the user requesting it, without the need to provision a server in every region ahead of time. But there's a catch: for most use cases, the application is bottlenecked by the database, which _does_ require provisioning a persistent server ahead of time. This can mean sacrificing the main benefit of serverless, as every cache miss needs to query a single master database in an arbitrary region, regardless of how close to the user the HTTP server is executing.

Seafowl solves this problem for analytical workloads, by accepting certain tradeoffs that might be less acceptable for transactional databases, in exchange for fast and cacheable responses to read queries. For example, as an OLAP database, Seafowl assumes that data is loaded into it periodically, and it's generally okay to return stale data while loading new data into it.

## Why Seafowl?

Seafowl separates compute and storage of the data it queries. It makes data in [object storage](https://seafowl.io/docs/guides/scaling-multiple-nodes#postgresql-as-a-catalog-minio-as-s3-storage) queryable by a Docker [image](https://hub.docker.com/r/splitgraph/seafowl) ([binary](https://github.com/splitgraph/seafowl/releases) also available), and maintains its internal catalog by writing to a SQLite file. This makes it easy to deploy as a serverless function to any platform that can run Docker images, like Google Cloud Run[^2]. With Seafowl you can expect a fast initialization time (e.g. 10ms) and configurable dependencies keep you in the driver's seat for deciding which tradeoffs make sense for your situation.

Another perk: because Seafowl offers a [CDN-friendly HTTP story](https://seafowl.io/docs/guides/querying-cache-cdn), if your data is suitable for caching your end users will likely further benefit if you choose to front with e.g. CloudFlare or similar CDN providers.

## What's the challenge?

The challenge is avoiding cold starts, while also avoiding the need for a persistent node for storing metadata. The Seafowl architecture is divided into three parts: compute (e.g. the binary, deployed in e.g. a Docker image); storage (the blob data in Object Storage); and a catalog (the metadata and information schema for the database). Compute and storage map naturally to services like GCR and GCS. But for the catalog, it's less clear. Seafowl supports writing catalog metadata to either a Postgres database, or a SQLite file. A Postgres catalog supports multiple writers, making each Seafowl instance able to act as a reader and a writer. This comes at the cost of requiring a persistent node (options like Neon[^1] exist, but in our testing, the latency was unacceptable as it caused cold starts of multiple seconds). A SQLite catalog can be bundled with the image, but at the cost of being able to actually persist Seafowl writes.


Is there a way we can get the best of both worlds? With GCS FUSE, we can come close. That's what we'll explore in this post. We're going to deploy Seafowl to GCR, and mount its catalog as a SQLite "file" on the FUSE filesystem backed by GCS. The end result is a serverless database that can respond to read queries from any region, with start times on the order of 10ms.

## Goal
In today's post we show you how to stand up serverless Seafowl on [Cloud Run](https://cloud.google.com/run).

GCP offers an "always free" tier which may cover both your [storage](https://cloud.google.com/free/docs/.free-cloud-features#storage) and [compute](https://cloud.google.com/free/docs/free-cloud-features#cloud-run) needs. This HOWTO seeks to stay within those limits, so you are likely to have low or possibly zero costs. Either way, this guide sequesters all resources we create here into a separate GCP project for easy cleanup.

## Requirements

- Access to [GCP](https://console.cloud.google.com/getting-started) (e.g. a Google account[^3])
- The `gcloud` CLI (aka Cloud SDK. [Need it?](https://cloud.google.com/sdk/docs/install-sdk))
- Choosing a unique-to-GCP
  - project name (`$PROJECT_NAME`)
  - bucket name (`$BUCKET_NAME`)

Ready? Let's begin!

1. After installing `gcloud`, login to your Google account.
```shell
gcloud auth login
```

2. Create a new project (optional, suggested).
```shell
gcloud projects create $PROJECT_NAME
Create in progress for [https://cloudresourcemanager.googleapis.com/v1/projects/seafowl-gcsfuse].
Waiting for [operations/cp.6073745129322302465] to finish...done.
Enabling service [cloudapis.googleapis.com] on project [seafowl-gcsfuse]...
Operation "operations/acat.p2-124549755242-c3e8de2b-a6fa-4a73-aaa1-ad949f26e7cb" finished successfully.
```
ℹ️ In case you get an "already in use" error, please choose a unique name. Consider appending some randomness and use `export PROJECT_NAME=YourName` to refer to it through the rest of the commands).

3. Set your project and region

Creating resources in the same region gives best performance as well as helps avoid cross-regional networking charges.
```shell
gcloud config set project $PROJECT_NAME
Updated property [core/project].
```

```shell
gcloud config set run/region $REGION # e.g. us-east1
Updated property [run/region].
```

4. Create a bucket

This bucket will be mounted as a pseudo-filesystem inside Seafowl's container.
```shell
gsutil mb -l us-east1 gs://$BUCKET_NAME
Creating gs://$BUCKET_NAME/...
```
ℹ️ Cloud Storage bucket names _share a global namespace_. Similarly to the project name, in case you encounter e.g. "The requested bucket name is not available", please pick a [unique name](https://cloud.google.com/storage/docs/buckets#naming) and `export BUCKET_NAME=MyBucket`.

5. Setup up Seafowl's config + credentials
- Seafowl can be configured via `seafowl.toml`. We persist it in GCP's [Secret Manager](https://console.cloud.google.com/security/secret-manager) so it can be mounted in our function.
```
[object_store]
type = "gcs"
bucket = "$BUCKET_NAME"
google_application_credentials = "seafowl-data/seafowl-gcsfuse.json"
[catalog]
type = "sqlite"
dsn = "seafowl-data/seafowl.sqlite"
[frontend.http]
bind_host = "0.0.0.0"
write_access = "efe507120a88bdb1b7a2e0479bdc4c163bc31c2d83787240dbccd8b95115d92a"
```
Replace with your real bucket name, then save the above config as `seafowl.toml`, and then run:

```shell
gcloud secrets create --data-file seafowl.toml seafowl_toml
```
(Allow the API to be enabled, if it asks)

- Upload the credential file into the bucket via:
```shell
gcloud storage cp seafowl-gcsfuse.json gs://$BUCKET_NAME/seafowl-gcsfuse.json
Copying file://seafowl-gcsfuse.json to gs://$BUCKET_NAME/seafowl-gcsfuse.json
  Completed files 1/1 | 2.3kiB/2.3kiB
```
NOTE: The [Secret Manager](https://console.cloud.google.com/security/secret-manager) is also suitable for this file.
In the interest of keeping a lid on costs we only save one secret. By mounting `seafowl-gcsfuse.json` via GCS Fuse, it's provided to Seafowl before it starts.

7. Establish least privilege permissions
We create a service account so that minimum needed permissions are sequestered into their own identity.
```shell
gcloud iam service-accounts create seafowl-gcsfuse-identity
```
Add a binding for object storage and accessing secrets.
```
gcloud projects add-iam-policy-binding $PROJECT_NAME \
     --member "serviceAccount:seafowl-gcsfuse-identity@$PROJECT_NAME.iam.gserviceaccount.com" \
     --role "roles/storage.objectAdmin" \
     --role="roles/secretmanager.secretAccessor"
Updated IAM policy for project [$PROJECT_NAME].
bindings:
- members:
  - user:user@example.com
  role: roles/owner
- members:
  - serviceAccount:seafowl-gcsfuse-identity@$PROJECT_NAME.iam.gserviceaccount.com
  role: roles/storage.objectAdmin
etag: BwX8Ze-ob4Y=
version: 1
```

8. Almost done! Now we can deploy the function:
```shell
gcloud run deploy seafowl-gcsfuse \
  --image splitgraph/seafowl-gcsfuse:latest \
  --execution-environment gen2 \
  --allow-unauthenticated \
  --service-account seafowl-gcsfuse-identity \
  --update-secrets=/app/config/seafowl.toml=seafowl_toml:latest \
  --update-env-vars BUCKET=$BUCKET_NAME

API [run.googleapis.com] not enabled on project [814934642383]. Would you like to enable and retry (this will take a few minutes)? (y/N)?  y

Enabling service [run.googleapis.com] on project [814934642383]...
```
[Dockerfile](https://github.com/splitgraph/seafowl-gcsfuse/blob/master/Dockerfile) available.

8. Check it with `curl`

Just swap your endpoint for the sample one.

### Read
```shell
curl -i -H "Content-Type: application/json" \
-X POST "https://seafowl-gcsfuse-YourEndpointHere.a.run.app/q" -d@- <<EOF
{"query": "
SELECT now()
"}
EOF
```
Should return something like:
```shell
HTTP/2 200
content-type: application/octet-stream
vary: Content-Type, Origin, X-Seafowl-Query
x-cloud-trace-context: 2f378466d69425e60793db49b4f406a4
date: Fri, 19 May 2023 14:57:37 GMT
server: Google Frontend
content-length: 43

{"now()":"2023-05-24T14:57:37.809652290Z"}
```

### Write
```shell
curl -i -H "Content-Type: application/json" \
-H "Authorization: Bearer Fc8yA8SZ0On70pg7znAu7s6EEHnDgUzP" \
-X POST "https://seafowl-gcsfuse-YourEndpointHere.a.run.app/q" -d@- <<EOF
{"query": "
CREATE TABLE holditright (id int)
"}
EOF
```

Should return
```shell
HTTP/2 200
content-type: application/octet-stream
vary: Content-Type, Origin, X-Seafowl-Query
x-cloud-trace-context: 8f75d4895dac499dbeef7eabd6fdfcba;o=1
date: Fri, 19 May 2023 16:36:09 GMT
server: Google Frontend
content-length: 0
```

### Verify
If you look at your bucket using the web console, you should see something roughly similar to this:
![Bucket](/static-v2/seafowl/blog/deploying-serverless-seafowl/bucket-example.jpg)

### Tear down
The function you just deployed is likely to be within the always free tier, but in case you'd like to clean up the project, run:
```
gcloud run services delete $SERVICE_NAME # e.g. seafowl-gcsfuse
gcloud config set project $PROJECT_NAME
gcloud config set run/region $REGION
```
Here's how to delete the [bucket](https://cloud.google.com/storage/docs/deleting-bucket) and the [service account](https://cloud.google.com/iam/docs/service-accounts-delete-undelete?hl=en#deleting).


## Conclusion
Congrats! 🎉 You have a scale to zero, web (HTTP)-first analytical database, deployed to the edge.
Ready to load up with whatever data you want, and ready to be fronted by a CDN.

![Low cloud bills are tasty](/static-v2/seafowl/blog/deploying-serverless-seafowl/rainbow-unsplash.com-@jorgefdezsalas.jpeg)


## Optional Follow Up
Two bonus ideas for consideration:
- For public & production deployments, a common next step is controlling who can write vs read. Consider deploying two functions: read-only for public access and write-enabled which is only available to trusted parties. Setting read-only can be done by providing [env var](https://seafowl.io/docs/reference/seafowl-toml-configuration#using-environment-variables) `SEAFOWL__FRONTEND__HTTP__WRITE_ACCESS=off` and `RUN_SEAFOWL_READ_ONLY=true` [info](https://github.com/splitgraph/seafowl-gcsfuse/blob/21376efa28c6ab7b7198e5394c3657b9d78002a1/gcsfuse_run.sh#L11).
- If your data is public, and your users would benefit from better latency, consider [fronting the Cloud Run endpoint with CloudFlare](https://seafowl.io/docs/getting-started/tutorial-fly-io/part-4-2-cdn-with-cloudflare). The CloudFlare CDN offers a convenient and powerful way to cache query results.


#### Image credits
[nikkotations](https://unsplash.com/@nikkotations), [jorgefdezsalas](https://unsplash.com/@jorgefdezsalas)

[^1]:
    More details [here](https://neon.tech/blog/quicker-serverless-postgres)

[^2]:
    Not your preferred cloud provider? Let us know if you'd like to see a [Lambda](https://aws.amazon.com/lambda/) or [Container Instance](https://azure.microsoft.com/en-us/products/container-instances) version of this post.

[^3]:
    This is roughly what you should see if you visit the [GCS console](https://console.cloud.google.com/) for the first time.

    <img src="/static-v2/seafowl/blog/deploying-serverless-seafowl/setup-gcs.png" />



Learn how to combine Seafowl with GCS FUSE to achieve true scale to zero. Serve users at the edge with a web (HTTP)-first analytical database that works on GCP Cloud Run, including within the "always free" tier.

Deploying a serverless Seafowl DB to Google Cloud Run using GCS FUSE and SQLite


![](/static-v2/og/blog/opendatamonitor.png)

## Tracking open government datasets
Say hello to [Open Data Monitor](https://open-data-monitor.splitgraph.io/), an open government dataset diff visualizer. Observe when [Socrata](https://dev.socrata.com/) datasets were added/deleted by day, week, and month, with upstream attribution and easy querying via the respective Splitgraph repository.

In a previous [post](https://www.splitgraph.com/blog/seafowl-sql-cdn) we discussed the benefits of Seafowl's approach to HTTP caching; in particular its effective use of [ETags](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/ETag) to deliver results on the edge with excellent latency characteristics. Today, we continue on our journey to:
- Ingest Splitgraph's Socrata catalog into Seafowl
- Insert tables that expose added/deleted status ("observe" Socrata)
- Swap Fly.io instances in a cost-effective way
- Build a mini Next.js app to render it all out in the browser, making sure to balance the best of SSR SEO benefits with Seafowl's browser-friendly cache semantics.

## Ingestion
In case you didn't already know, Splitgraph maintains a catalog of Socrata datasets, stored as a [Splitgraph repository at `splitgraph/socrata`](https://www.splitgraph.com/splitgraph/socrata/). Additionally, Splitgraph actually _indexes_ all of the 40,000+ datasets and makes each one queryable at its own URL (like [`cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r`](https://www.splitgraph.com.cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r)) and Splitgraph repository on the Data Delivery Network (DDN). It's possible to point your favorite Postgres client to the DDN and query arbitrary Socrata datasets in a familiar way. From [traffic accidents](https://neumark.github.io/splitgraph-map-demo/) in Fort Worth, TX [^1], to [taxi rides](https://nyctaxis.surge.sh/) as a proxy for "how's NYC Covid bounce back going"[^2], we've built several integrations that present data in visual and interactive ways without custom plumbing.
We also made [Socrata roulette](https://splitgraph.github.io/socrata-roulette/) for generating random queries against all of the available datasets.

As useful as the DDN is, today we will demonstrate how [Seafowl](https://seafowl.io) can make for an effective alternative that performs with low latency and generally has a smaller resource footprint. First, by starting with this catalog, we [fetch](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L42) all Socrata images courtesy of Splitgraph's GQL API. (Conveniently we can export directly in [Parquet format](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/ingest.py#L141)!) Then, we [insert](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L213) the results into Seafowl tables. This whole job is powered by Github [Actions](https://docs.github.com/en/actions), and occurs on a regular basis (currently nightly). After we've ingested the new state of all Socrata datasets, we [pre-compute a diff across time](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/sql/daily_diff.sql) of all datasets that were added or removed on a given day. We also downsample these diffs to week-over-week and month-over-month changes.

Because this involves a self-join of a table with several million rows, this currently requires a large amount of memory as you'll see later.

##  Dynamically scaling our Fly.io instance for ingestion
Similarly to the [Seafowl tutorial](https://seafowl.io/docs/getting-started/tutorial-fly-io/introduction), we again went with Fly.io for hosting. While the free tier generally offers enough performance for Open Data Monitor's read-heavy workload, when it comes to _ingestion_ and building the day-over-day diff table, this time we ran into memory issues because of a self-join. It is perhaps unsurprising that as Socrata has grown over time, so have the number and size of datasets on offer. That's why we added a step in the GHA to temporarily [scale up](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/sync.yml#L30) the Fly.io instance memory for ingestion purposes. We also include an [insurance policy](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/downsize-insurance.yml) to help reduce risk of job errors causing unexpected cloud costs.

## Rendering in the browser
With a loaded Seafowl instance ready to go, we can now build a [Next.js app](https://github.com/splitgraph/open-data-monitor) so we can fetch from Seafowl and render it in the browser.

By default, we render the current week, but the user can specify a certain day, week, or month.

## Disappearing data examples

<div style={{display: 'flex'}}>
<div>
Because traversing over time is now trivial, we can surface curious moments.
<br /><br />
For example, just a few clicks moving backwards from January 2023 yielded disappearing

- [greenhouse gas emissions](https://open-data-monitor.splitgraph.io/2023-01-13#data.bayareametro.gov)
- [commercial ovens](https://open-data-monitor.splitgraph.io/2023-01-13#data.energystar.gov)
- [hot spots](https://open-data-monitor.splitgraph.io/2023-01-05) 🤔
- [Austin Schools Rated as Improvement Required](https://open-data-monitor.splitgraph.io/2022-12-31#data.austintexas.gov)

</div>
<img src="/static-v2/seafowl/blog/open-data-monitor/opendatamonitor-hero-2.png"
  style={{float: 'right', width: '300px', paddingLeft: '2em'}}
  alt="detective smoking a pipe"
/>
</div>


## SSR by default, but browser talks to Seafowl directly on nav
To strike a balance between SSR-conferred SEO advantages, and Seafowl's HTTP cache friendly defaults, we pre-fetch inside [getInitialProps](https://github.com/splitgraph/open-data-monitor/blob/153e4464eb24994ce5503680ce4b907a8e1faa8d/pages/index.tsx#L26), which means the page renders properly even with JS disabled.
But since we don't want the Vercel server to intermediate subsequent fetches, when the user clicks Previous/Next, their browser requests from Seafowl directly. Because we are using the GET API, we benefit from the nice cache behavior, so subsequent loads are nice and zippy, even on Fly.io's free tier, and even if the actual ingest was a memory hungry job.

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/opendatamonitor-demo.mp4" type="video/mp4" />
</video>

## Read the code

The code for all of this is available on GitHub, in two repositories:

- [splitgraph/socrata-to-seafowl](https://github.com/splitgraph/socrata-to-seafowl): GitHub Actions for syncing from Socrata to Seafowl instance hosted on Fly.io
- [splitgraph/open-data-monitor](https://github.com/splitgraph/open-data-monitor): The Next.js frontend application



## Conclusion

We hope this post has illustrated a pathway to combine Seafowl's "analytics at the edge" benefits with Socrata's bevy of government data, in a cost-effective way.
While today we focused on open government data, regardless of the data you're working with, consider checking out what Seafowl offers.

If you discover any curious dataset mysteries, or build an integration with Seafowl, we'd love to hear from you. Tweet us [@splitgraph](https://twitter.com/intent/tweet?text=@splitgraph).

Happy sleuthing!


[^1]: https://www.splitgraph.com/jumpstart/mapping-geo-data
[^2]: https://github.com/splitgraph/nyctaxis


##### Image credits
[mediamodifier](https://unsplash.com/photos/yx17UuZw1Ck), [sammywilliams](https://unsplash.com/photos/ocvLVIrC7c0), [ayosake](https://unsplash.com/photos/esOA3o92zxE)


How can we track open government datasets over time? Say hello to Open Data Monitor, a Socrata tracking tool powered by Seafowl and Splitgraph.

Open Data Monitor


![hero](/static-v2/images/blog/select-from-the-browser/hero.jpg)

## Serverless SQL Queries

Frontend developers who write SQL have traditionally been required to sequester
their SQL queries to a backend server. The popular
[adage](https://stackoverflow.com/a/60332563/2044952) has more or less been
'don't run SQL directly from the frontend.'

For Splitgraph users, it turns out the old rules no longer apply. Thanks to the
[DDN HTTP API](https://www.splitgraph.com/docs/query/ddn-http), the browser can
work directly with raw SQL queries. In this post we demonstrate by building a
client-side only dashboard with taxi ride data.

## In a nutshell

Today we build a 100% client-side JavaScript app. We will:
- download official NYC taxi data and publish it to a Splitgraph repo
- write some SQL to roll up taxi zone activity across a 24h period
- clone and run a SPA to present taxi activity on an interactive map of NYC

## Dashboard goal: NYC Covid recovery followup
Whereas we previously focused on
[subway rides](./data-driven-app-powered-by-splitgraph), cab rides
are also a familiar sight to New Yorkers. Rendering taxi activity on a map of
NYC may give us additional indicators into how the Covid recovery is going.

I assume you have a Splitgraph account
([need one?](https://www.splitgraph.com/auth/sign_up)), Node.js, and the
[sgr](https://www.splitgraph.com/docs/sgr-advanced/getting-started/installation#installation-1)
CLI available.

## Download the data
Taxi ride datasets are published at
[nyc.gov](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) on a
monthly basis. April is the most recent month available at the time of writing,
so let's download yellow cab records for April, across 2020, 2021, and 2022. It
looks something like this:

![April 2022](/static-v2/images/blog/select-from-the-browser/download.jpg)

Note: as of May 2022 nyc.gov started publishing this data in Parquet format, but
we need CSV. Thus please convert however you prefer.

<details>
<summary style={{display: "list-item"}}><b>parquet2csv.py helper (optional)</b></summary>

A [script](https://github.com/splitgraph/nyctaxis/master/) is available. It
depends on `pandas` and `fastparquet`.

```shell
pip install pandas fastparquet
```

</details>

However you make your CSVs once they're ready, please upload them via the `sgr` CLI.

```shell
$ sgr cloud upload $yourSplitgraphUser/nyctaxi yellow_tripdata_2020-04.csv yellow_tripdata_2021-04.csv yellow_tripdata_2022-04.csv
```
![Upload](/static-v2/images/blog/select-from-the-browser/upload.jpg)

Once the CSVs have been ingested your repo should look like this:
![Table](/static-v2/images/blog/select-from-the-browser/table.jpg)

Download and publish taxi data to Splitgraph: ✅

## SQL rollup

To populate the taxi zones with a 24 hour sample, let's
[roll up](https://github.com/splitgraph/nyctaxis/master/ddn-fetcher.ts#L6) all taxi
pickups and drop-offs that occurred during the given day.

<details>
<summary style={{display: "list-item"}}>
<b>Want to see the query?</b>
</summary>

<EmbeddedQuery
  minHeight={600}
  query={`WITH
pickups AS
(
SELECT
  "PULocationID" as location_id,
  COUNT("PULocationID") as pickup_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
  tpep_pickup_datetime >= '2022-04-01'
AND
  tpep_pickup_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "PULocationID"
),
dropoffs AS
(
SELECT
  "DOLocationID" as location_id,
  COUNT("DOLocationID") as dropoff_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
  tpep_dropoff_datetime >= '2022-04-01'
AND
  tpep_dropoff_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "DOLocationID"
),
joined AS
(
SELECT
  pickups.location_id, pickup_count, dropoff_count
FROM
  pickups LEFT JOIN dropoffs ON pickups.location_id = dropoffs.location_id
)
SELECT * from joined`}
  layout="hsplit"
/>
</details>

Roll up taxi activity: ✅

## Clone the React app

The [repo](https://www.github.com/splitgraph/nyctaxis) is available on GitHub.

<details>
<summary style={{display: "list-item"}}>
<b>Want to step through the code tag by tag?</b>
</summary>

- [Initial CRA](https://github.com/splitgraph/nyctaxis/tree/setup-cra) Tag: `setup-cra`
- [Add taxi zone data](https://github.com/splitgraph/nyctaxis/tree/taxizone-data) Tag: `taxizone-data`
- [Handle fetching + mapping](https://github.com/splitgraph/nyctaxis/tree/fetch-and-map) Tag: `fetch-and-map`

</details>

Since we use `mapbox-gl` for the interactive map, get yourself
a free API key at [mapbox.com](https://www.mapbox.com/) if you need one.

Changing the date and clicking 'load' will cause new data to be fetched and
rendered on the app.

Clone and run the SPA: ✅

## Conclusion
The DDN HTTP API offers a convenient way to move beyond the old rules of the
road, and let frontend developers run queries from their end user's browser.

Thanks for reading and keep following the Splitgraph blog for more posts.

##### Image credit
[Eduards Ceravs](https://flic.kr/p/b67zX6)


How Splitgraph's DDN HTTP API lets you run SQL queries directly from the browser, opening new possiblities for client-side data-driven apps.

SELECT directly from the browser


## Introduction
Data-driven apps like interactive dashboards offer a valuable way for users to more easily draw individual conclusions backed by data. However, delivering those benefits may require significant cross-discipline engineering skills: backend, frontend, data ingestion/pipeline, DevOps and potentially infra to run all of it.

In this post we illustrate how Splitgraph helps lower barriers for data-driven apps by addressing key pain points. The Splitgraph DDN integrates smoothly with anything that speaks Postgres, and oftentimes reduces the need to stand up and maintain infra.

Today we will:
- upload a CSV to a Splitgraph repo
- use SQL to query the repo via the DDN
- use Python to parameterize the queries and do basic aggregations
- tie it all together with [Streamlit](https://streamlit.io) in a rich web UI

## Dashboard idea: How is NYC's Covid recovery going?
To answer this question one might consider various year-over-year data, e.g. real estate [occupancy rates](https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4124698), sales tax [revenue](https://comptroller.nyc.gov/reports/annual-state-of-the-citys-economy-and-finances/), or [Broadway](https://www.broadwayleague.com/research/grosses-broadway-nyc) ticket sales.

How about public transit? NYC has one of the world's largest metro systems, and it turns out turnstile activity is regularly [published](http://web.mta.info/developers/turnstile.html) by the MTA. While not every New Yorker uses the subway, many consider it vital, and such data could serve as a reasonable indicator of whether city life is back to pre-pandemic levels (spoiler: or not, as it turns out).

OK, we've got a dashboard goal! Let's plot data for NYC's most popular subway stations, by month, across 2020, 2021, and 2022.

I assume you have a Splitgraph account ([need one?](https://www.splitgraph.com/auth/sign_up)) and Python 3.x installed.

## Enter Splitgraph
<details>
<summary style={{display: "list-item"}}><b>Just want to see the code?</b></summary>

A self-contained [repo](https://www.github.com/splitgraph/nyc-subway-dashboard) is available on GitHub.

Each step has been tagged:
- [Connect to DDN](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) Tag: `sql-querying`
- [Render the first graph via Streamlit](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/first-graph) Tag: `first-graph`
- [Year-over-year graphs](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) Tag: `year-over-year-graphs`

</details>


#### Upload the CSV
A [totals rollup CSV](https://static.observableusercontent.com/files/ad48776aa4a65719ef8b57810eb825aad469ee25f5b216efce578bfa69a6df47664590d2a58927ab0a32eac164428a5c750e88669aacdc4f1d4d22c3dbb5f51d?response-content-disposition=attachment%3Bfilename*%3DUTF-8%27%27mta_all_points%25402.csv) is a good starting point and what we're going to use. Go ahead and download it.


With the CSV in hand, you can drag it here (yes, right into this blog post):
<EmbeddedUpload />

If you prefer the [CLI](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-upload) that is also an option. A [previous blogpost](https://splitgraph.com/blog/csv-to-sql-upload-to-data-delivery-network-ddn) covers uploading in more detail.

![Drag and drop the CSV](/static-v2/images/blog/data-driven-app/upload-step-1.png)
_Drag and drop the CSV_

![I went with "nyc-transit" for a repo name](/static-v2/images/blog/data-driven-app/upload-step-2.png)
_I went with "nyc-transit" for a repo name_

![Repo page view](/static-v2/images/blog/data-driven-app/upload-step-3.png)
_Repo page view_

Upload a CSV to a Splitgraph repo: ✅

#### Let's query it!
With the repo now hosting the data, let's query it using SQL.

Here's a query for January 2020 turnstile activity:
<EmbeddedQuery
  query={`SELECT "START_DATE", "STATION", SUM("TOTAL")
FROM "paws/nyc-transit"."turnstile-activity"
WHERE to_date("START_DATE", 'MM/DD/YYYY') BETWEEN '1/1/2022' AND '2/1/2022'
GROUP BY "START_DATE", "STATION"`}
  layout="hsplit"
/>


#### DDN Time
For querying the repo in our app, we'll use the Splitgraph DDN. This feature makes repos conveniently available via `postgresql://data.splitgraph.com:5432/ddn`, and most Postgres clients should work. You will need a Splitgraph API key and secret to access the DDN; generate those by going to your [account settings](https://www.splitgraph.com/settings/sql-credentials) and set them aside.

The following example snippet will connect a Python app to the DDN. Swap user & password for the creds you just made.

```python
conn = psycopg2.connect(
    user = "myuser",
    password = "supersecret",
    host = "data.splitgraph.com"
    port = "5432"
    database = "ddn"
)
```

If you prefer you can check out a working example repo at this step. [Source](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) - just [add the creds](https://docs.streamlit.io/streamlit-cloud/get-started/deploy-an-app/connect-to-data-sources/secrets-management) to `.streamlit/secrets.toml`

Use SQL to query the repo: ✅

## Enter Streamlit
<details>
<summary style={{display: "list-item"}}><b>Prefer an empty slate?</b></summary>

Streamlit has an [example](https://github.com/streamlit/streamlit-example) repo ready to clone/fork.

</details>

## Handling aggregations and data cleanup with Python
We have all this data available to work with from the DDN. Now, let's write some Python to clean it up and prepare it for plotting.
Let's do a simple aggregation so we can pass it into a Pandas DataFrame.


```python
data = {"DATE": [], "TOTAL": []}
for k, v in totals.items():
    data['DATE'].append(k)
    data['TOTAL'].append(v)

source = pd.DataFrame.from_dict(data)
line_chart = alt.Chart(source).mark_line().encode(
        alt.X('DATE:T', title='Time'),
        alt.Y('TOTAL:Q', title='Weekly rides'),
    ).properties(title="MTA total weekly turnstile activity (Jan 2020-May 2022)")
```
The [rest of the code](https://github.com/splitgraph/nyc-subway-dashboard/blob/master/streamlit_app.py#L45) is available.

Use Python to do basic aggregations: ✅


## How to integrate Splitgraph with Streamlit
Streamlit lets Python developers write interactive dashboards, with sliders, inputs, and graphs, all without having to write a single line of JavaScript.

For example `st.slider()` will render a slider on the page whose value is available as a variable.
```python
month = st.slider("Choose a month (Jan = 1, Feb = 2, etc.)", min_value=1, max_value=5)
```
Consider seeing the [tag](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) for a fleshed out example.

Tie it all together with Streamlit: ✅

## Conclusion
Is NYC back to business as usual? Well, take a look for yourself:
![January activity, year-over-year](/static-v2/images/blog/data-driven-app/jan-year-over-year.png)

How about April?
![April activity, year-over-year](/static-v2/images/blog/data-driven-app/apr-year-over-year.png)

In sum, while we can see popular stations are getting more traffic than the early days of lockdown, so far turnstile activity in 2022 hovers around 50% of the same month in 2020.

The actual [dashboard](https://share.streamlit.io/splitgraph/nyc-subway-dashboard) has mouseover with specific #s.

## Recap
We started with a simple dataset, uploaded it to a Splitgraph repo so we could run SQL queries.
Then, by writing some basic Python, we powered a mini dashboard by utilizing Streamlit's rich UI features.
We didn't need to stand up any new infra, and our users are empowered to draw their own conclusions.

## Shoutouts & Acknowledgements
Thank you MTA for regularly publishing [turnstile data](http://web.mta.info/developers/turnstile.html)

Thank you [@je2538](https://observablehq.com/@je2538/bouncing-back-where-nyc-subway-riders-have-returned-and-whe), which is how I learned that the MTA offers this, and who (I believe) rolled up the data in a suitable format for this exercise.

Thank you [Streamlit](https://streamlit.io/about) for making it easier to serve a rich client UI from Python.


We demonstrate how combining Splitgraph and Streamlit lets devs and data scientists more easily build data-driven apps. In this example we plot NYC subway turnstile data to try and glean how NYC's Covid recovery is going.

Splitgraph has been acquired by EDB! Read the blog post.

By Patrick Skinner

Writing UDFs in Golang

Deploying a serverless Seafowl DB to Google Cloud Run using GCS FUSE and SQLite

Open Data Monitor

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Product

Support

Company

Splitgraph

Splitgraph has been acquired by EDB! Read the blog post.

By Patrick Skinner

Writing UDFs in Golang

Deploying a serverless Seafowl DB to Google Cloud Run using GCS FUSE and SQLite

Open Data Monitor

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Product

Support

Company

Community

Splitgraph